package DBLayer;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

import ModelLayer.Customer;
import ModelLayer.Invoice;
import ModelLayer.Sale;
import ModelLayer.Enum;
import ModelLayer.SaleLine;

public class DBSale implements IFDBSale {
	private Connection con;

	public DBSale() {
		con = DBConnection.getInstance().getDBcon();
	}

	@Override
	public Sale getSale(int saleId, boolean retrieveAssociation) {
		String wClause = "  saleId = '" + saleId + "'";
		return singleWhere(wClause, retrieveAssociation);
	}

	@Override
	public int insertSale(Sale sale) throws Exception {
		int nextId = GetMaxId.getMaxId("Select max(saleId) From Sale") + 1;
		int rc = -1;
		SimpleDateFormat df = new SimpleDateFormat("dd/MM/yyyy");
		String date = df.format(sale.getDate());
		String deliverDate = df.format(sale.getDeliveryDate());
		String querySale = "INSERT INTO sale(saleId, createDate, deliverStatus, deliveryDate, customer_Id, invoice_No, totalPrice) VALUES('"
				+ nextId
				+ "','"
				+ date
				+ "','"
				+ sale.getDeliverStatus().toString()
				+ "','"
				+ deliverDate
				+ "','"
				+ sale.getCustomer().getId()
				+ "','"
				+ sale.getInvoice().getInvoiceNo()
				+ "','"
				+ sale.getTotalPrice() + "')";

		System.out.println("insert : " + querySale);
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(querySale);
			stmt.close();
		} catch (SQLException ex) {
			System.out.println("Sale is not inserted");
			throw new Exception("Sale is not inserted correct");
		}
		return nextId;
	}

	@Override
	public ArrayList<Sale> getAllSale(boolean retrieveAssociation) {
		return miscWhere("", retrieveAssociation);
	}

	@Override
	public int updateSale(Sale sale) {
		Sale saleObj = sale;
		int rc = -1;
		SimpleDateFormat df = new SimpleDateFormat("dd/MM/yyyy");
		String date = df.format(sale.getDate());
		String deliverDate = df.format(sale.getDeliveryDate());
		String query = "UPDATE sale SET " + "createDate = '" + date + "',"
				+ "deliverStatus = '" + saleObj.getDeliverStatus().toString()
				+ "'," + "deliveryDate = '" + deliverDate + "',"
				+ "customer_Id = '" + saleObj.getCustomer().getId() + "',"
				+ "invoice_No = '" + saleObj.getInvoice().getInvoiceNo() + "',"
				+ "totalPrice = '" + saleObj.getTotalPrice() + "'"
				+ " WHERE saleId = '" + saleObj.getSaleId() + "'";
		System.out.println("Update query:" + query);
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		} catch (Exception e) {
			System.out.println("Update exception in sale db: " + e);
		}
		return rc;
	}

	@Override
	public int deleteSale(int saleId) {
		int rc = -1;
		String query = "DELETE FROM sale WHERE saleId = '" + saleId + "'";
		System.out.println(query);
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		} catch (Exception ex) {
			System.out.println("Delete exception in sale db: " + ex);
		}
		return (rc);
	}

	private Sale singleWhere(String wClause, boolean retrieveAssociation) {
		ResultSet results;
		Sale sale = new Sale();
		String query = buildQuery(wClause);
		System.out.println(query);
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			if (results.next()) {
				sale = buildSale(results);
				stmt.close();
				if (retrieveAssociation) {
					IFDBCustomer dbCustomer = new DBCustomer();
					Customer customer = dbCustomer.getCustomer(sale
							.getCustomer().getId(), true);
					sale.setCustomer(customer);
					System.out.println("Customer is selected");

					// retrieve saleline
					IFDBSaleLine dbSaleLine = new DBSaleLine();
					ArrayList<SaleLine> salelines = dbSaleLine
							.getAllSaleLineForSale(sale.getSaleId(), false);
					sale.setSaleLines(salelines);
					System.out.println("Salelines are selected");

					// retrieve invoice
					IFDBInvoice dbInvoice = new DBInvoice();
					Invoice invoice = dbInvoice.getInvoice(sale.getInvoice()
							.getInvoiceNo(), true);
					sale.setInvoice(invoice);
					System.out.println("Invoice is selected");
				} else {
					sale = null;
				}
			}
		} catch (Exception e) {
			System.out.println("Query exception: " + e);
		}
		return sale;
	}

	private ArrayList<Sale> miscWhere(String wClause,
			boolean retrieveAssociation) {
		ResultSet results;
		ArrayList<Sale> list = new ArrayList<Sale>();
		String query = buildQuery(wClause);
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			while (results.next()) {
				Sale sale = new Sale();
				sale = buildSale(results);
				list.add(sale);
			}
			stmt.close();
			if (retrieveAssociation) {
				for (Sale sale : list) {
					IFDBCustomer dbCustomer = new DBCustomer();
					Customer customer = dbCustomer.getCustomer(sale
							.getCustomer().getId(), false);
					sale.setCustomer(customer);
					System.out.println("Customer is selected");

					// retrieve saleline
					IFDBSaleLine dbSaleLine = new DBSaleLine();
					ArrayList<SaleLine> salelines = dbSaleLine
							.getAllSaleLineForSale(sale.getSaleId(), false);
					sale.setSaleLines(salelines);
					System.out.println("Salelines are selected");

					// retrieve invoice
					IFDBInvoice dbInvoice = new DBInvoice();
					Invoice invoice = dbInvoice.getInvoice(sale.getInvoice()
							.getInvoiceNo(), false);
					sale.setInvoice(invoice);
					System.out.println("Invoice is selected");
				}
			}
		} catch (Exception e) {
			System.out.println("Query exception - select: " + e);
			e.printStackTrace();
		}
		return list;
	}

	private Sale buildSale(ResultSet results) throws ParseException,
			SQLException {
		Sale sale = new Sale();
		DateFormat df = new SimpleDateFormat("dd/MM/yyyy");
		Date createDate = df.parse(results.getString("createDate"));
		Date deliverDate = df.parse(results.getString("deliveryDate"));
		try {
			sale.setSaleId(results.getInt("saleId"));
			sale.setDate(createDate);
			sale.setDeliverStatus(Enum.valueOf(results
					.getString("deliverStatus")));
			sale.setDeliveryDate(deliverDate);
			sale.setTotalPrice(results.getDouble("totalPrice"));
			sale.setCustomer(new Customer(results.getInt("customer_Id")));
			sale.setInvoice(new Invoice(results.getInt("invoice_No")));
		} catch (Exception e) {
			System.out.println("error in building the sale object");
		}
		return sale;
	}

	private String buildQuery(String wClause) {
		String query = "SELECT saleId, createDate, deliverStatus, deliveryDate, customer_Id, invoice_No, totalPrice FROM sale";
		if (wClause.length() > 0) {
			query = query + " WHERE" + wClause;
		}
		return query;
	}
}